/**
 * 
 */
package pits.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import pits.dto.CompanyDTO;
import pits.dto.DepartexpDTO;
import pits.dto.DepartmentDTO;
import pits.dto.RoleDTO;
import pits.form.EditTakenCompanyInfoForm;
import pits.util.Constants;

/**
 * DAO class for getting employee's experiences
 * 
 * @author ltd
 * 
 */
public class EditTakenCompanyInfoDAO {

	Logger log = Logger.getLogger("EditTakenCompanyInfoDAO");

	public List<DepartexpDTO> getDepartexps(int empID) {

		log.info("EditTakenCompanyInfoDAO.getDepartexps start");

		List<DepartexpDTO> result = new ArrayList<DepartexpDTO>();
		DepartexpDTO dto = null;
		DepartmentDTO dtoDep = null;
		CompanyDTO dtoCom = null;
		RoleDTO dtoRole = null;
		Connection conn = null;
		String query = Constants.BLANK_VALUE;
		ResultSet rs = null;
		PreparedStatement pStat = null;

		try {
			query = "select t3.ComName, t3.Address, t3.Phone, t2.DepartName, t2.CompanyID, "
			        + "t1.StartDate, t1.EndDate, t4.Role, t4.RoleID, t1.Description, t2.DepartID "
			        + "from 	departexp as t1, department as t2, company as t3, role as t4 "
			        + "where 	t1.EmpID=? and t1.DepartID=t2.DepartID and t1.RoleID=t4.RoleID "
			        + "and t2.CompanyID=t3.CompanyID and t1.isDeleted=0 "
			        + "and t2.isDeleted=0 and t3.isDeleted=0 and t4.isDeleted=0 ";
			conn = pits.util.Connection.getConnection();
			pStat = conn.prepareStatement(query);
			pStat.setInt(1, empID);
			rs = pStat.executeQuery();
			while (rs.next()) {
				dtoCom = new CompanyDTO();
				dtoDep = new DepartmentDTO();
				dto = new DepartexpDTO();
				dtoRole = new RoleDTO();
				dtoCom.setComName(rs.getString("ComName"));
				dtoCom.setAddress(rs.getString("Address"));
				dtoCom.setPhone(rs.getString("Phone"));
				dtoCom.setCompanyID(rs.getInt("CompanyID"));
				dtoDep.setCompany(dtoCom);
				dtoDep.setDepartName(rs.getString("DepartName"));
				dtoDep.setDepartID(rs.getInt("DepartID"));
				dto.setDepartment(dtoDep);
				dto.setStartDate(rs.getString("StartDate"));
				dto.setEndDate(rs.getString("EndDate"));
				dto.setDescription(rs.getString("Description").trim());
				dtoRole.setRole(rs.getString("Role"));
				dtoRole.setRoleID(rs.getInt("RoleID"));
				dto.setRole(dtoRole);
				result.add(dto);
				log.info("CompanyID = " + dto.getDepartment().getCompany().getCompanyID());
			}
		} catch (Exception sqlEx) {
			log.error("EditTakenCompanyInfoForm.getDepartexps " + sqlEx);
		} finally {
			try {
				rs.close();
				pStat.close();
				conn.close();
			} catch (SQLException sqlEx) {
				log.error("EditTakenCompanyInfoForm.getDepartexps " + sqlEx);
			} catch (Exception ex) {
				log.error("EditTakenCompanyInfoForm.getDepartexps " + ex.getStackTrace());
			}
		}

		log.info("EditTakenCompanyInfoDAO.getDepartexps end");
		// new
		// pits.util.Logger().write("EditTakenCompanyInfoDAO.getDepartexps: Done");
		return result;
	}

	public List<DepartmentDTO> getDepartmentList(int comID) {

		Logger log = Logger.getLogger("EditTakenCompanyInfoDAO");

		List<DepartmentDTO> result = new ArrayList<DepartmentDTO>();
		DepartmentDTO dto = null;
		Connection conn = null;
		String query = Constants.BLANK_VALUE;
		ResultSet rs = null;
		PreparedStatement pStat = null;

		try {
			query = "select DepartID, DepartName " + "from   department " + "where  CompanyID=? and isDeleted=0";
			conn = pits.util.Connection.getConnection();
			pStat = conn.prepareStatement(query);
			pStat.setInt(1, comID);
			rs = pStat.executeQuery();
			while (rs.next()) {
				dto = new DepartmentDTO();
				dto.setDepartID(rs.getInt("DepartID"));
				dto.setDepartName(rs.getString("DepartName"));
				result.add(dto);
			}
		} catch (Exception sqlEx) {
			log.error("EditTakenCompanyInfoForm.getDepartmentList " + sqlEx);
		} finally {
			try {
				rs.close();
				pStat.close();
				conn.close();
			} catch (SQLException sqlEx) {
				log.error("EditTakenCompanyInfoForm.getDepartmentList " + sqlEx);
			} catch (Exception ex) {
				log.error("EditTakenCompanyInfoForm.getDepartmentList " + ex.getStackTrace());
			}
		}

		log.info("EditTakenCompanyInfoDAO.getDepartmentList end");
		return result;
	}

	public List<DepartexpDTO> updateTakenCompanyInfo(EditTakenCompanyInfoForm form, int departID, int empID) {

		log.info("EditTakenConpanyInfoForm.update start");

		List<DepartexpDTO> result = new ArrayList<DepartexpDTO>();
		Connection conn = null;
		String query = Constants.BLANK_VALUE;
		PreparedStatement pStat = null;
		ResultSet rs = null;
		int roleID = -1;

		try {
			conn = pits.util.Connection.getConnection();
			query = "select RoleID " + "from role " + "where Role=? ";
			pStat = conn.prepareStatement(query);
			pStat.setString(1, form.getRole());
			rs = pStat.executeQuery();
			while (rs.next()) {
				roleID = rs.getInt("RoleID");
			}
			if (roleID != -1) {
				query = "update departexp " + "set DepartID=?, StartDate=?, EndDate=?, RoleID=?, Description=? "
				        + "where DepartID=? and EmpID=? ";
				pStat = conn.prepareStatement(query);
				pStat.setInt(1, form.getDepartID());
				pStat.setString(2, form.getStartDate());
				pStat.setString(3, form.getEndDate());
				pStat.setInt(4, roleID);
				pStat.setString(5, form.getDescription());
				pStat.setInt(6, departID);
				pStat.setInt(7, empID);
				pStat.executeUpdate();
				result = getDepartexps(empID);
			} else {
				log.error("EditTakenCompanyInfoDAO.update failed in updating taken company information.");
			}
		} catch (Exception sqlEx) {
			log.error("EditTakenCompanyInfoDAO.update " + sqlEx);
		} finally {
			try {
				pStat.close();
				conn.close();
			} catch (SQLException sqlEx) {
				log.error("EditTakenCompanyInfoDAO.update " + sqlEx);
			}
		}

		return result;
	}

	public List<DepartexpDTO> deleteTakenCompanyInfo(String str, int empID) {

		log.info("EditTakenCompanyInfoDAO.delete start");

		List<DepartexpDTO> result = new ArrayList<DepartexpDTO>();
		Connection conn = null;
		PreparedStatement pStat = null;
		String query = Constants.BLANK_VALUE;
		String[] l = str.split(",");

		for (int i = 0; i < l.length; i++) {
			try {
				query = "delete from departexp " + "where EmpID=? and DepartID=? ";
				conn = pits.util.Connection.getConnection();
				pStat = conn.prepareStatement(query);
				pStat.setInt(1, empID);
				pStat.setInt(2, Integer.parseInt(l[i]));
				pStat.executeUpdate();
			} catch (Exception sqlEx) {
				log.error("EditTakenCompanyInfoDAO.delete " + sqlEx);
			} finally {
				try {
					pStat.close();
					conn.close();
				} catch (SQLException sqlEx) {
					log.error("EditTakenCompanyInfoDAO.delete " + sqlEx);
				}
			}
		}
		result = getDepartexps(empID);

		return result;
	}
}
